package net.valerijtimakov.homefinances.dao.providers;

import com.mysql.jdbc.Blob;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.Statement;
import java.math.BigDecimal;
import java.sql.*;
import net.valerijtimakov.homefinances.dao.storages.finances.mysql.IMySqlProvider;
import net.valerijtimakov.homefinances.exceptions.AppPersProviderException;
import org.apache.log4j.Logger;


public class MySqlProvider implements IMySqlProvider {
	
	private static final Logger logger = Logger.getLogger(MySqlProvider.class);
	
	private Connection conn = null;
	private Statement stmt = null;
	private ResultSet rs = null;
	private String hostName;
	private String dbName;
	private String user;
	
	public MySqlProvider(String hostName, String dbName, String user, String pass) throws AppPersProviderException {
		this.hostName = hostName;
		this.dbName = dbName;
		this.user = user;
		String url = "jdbc:mysql://"+hostName+"/"+dbName
				+"?noDatetimeStringSync=true&"
				+"zeroDateTimeBehavior=convertToNull&"
				+"jdbcCompliantTruncation=false";
		try {
			Class.forName ("com.mysql.jdbc.Driver").newInstance ();
			conn = (Connection) DriverManager.getConnection(url, user, pass);
		} catch (Exception e) {
			throw new AppPersProviderException("Не удалось создать соединение с БД!", e);
		}
	}
	
	public String getHostName() {
		return hostName;
	}

	public String getDbName() {
		return dbName;
	}

	public String getUser() {
		return user;
	}

	@Override
	public ResultSet getResultSet() {
		return rs;
	}
	
	private void initStatment() throws AppPersProviderException, SQLException {
		if (stmt == null){
			if(conn == null) {
				throw new AppPersProviderException("Потеряно соденинение с базой!");
			}
			stmt = (Statement) conn.createStatement();
		}
	}
	
	private void refreshResultSet() {
		try{ 
			if (rs != null) { 
				rs.close(); 
				rs = null; 
			}
		} catch (Exception e) {
			logger.error("Ошибка закрытия старого ResultSet!", e);
		}
	}
	
	@Override
	public int query(String sql) throws AppPersProviderException {
		try{
			initStatment();
			refreshResultSet();
			rs = stmt.executeQuery(sql);
			int count;
			rs.last();
			count = rs.getRow();
			rs.beforeFirst();
			return count;
		} catch (SQLException e) {
			throw new AppPersProviderException("Ошибка выполнения запроса!", e);
		} catch (Exception e) {
			throw new AppPersProviderException("Ошибка выполнения запроса!", e);
		}
	}
	
	@Override
	public long insert(String sql) throws AppPersProviderException {
		try{
			initStatment();
			stmt.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
			long last = UNDEFINED_LAST_INSERT_ID;
			refreshResultSet();
			rs = stmt.getGeneratedKeys();
			while (rs.next()){
				last = rs.getLong(1);
			}
			return last;
		} catch (SQLException e) {
			throw new AppPersProviderException("Ошибка выполнения запроса!", e);
		} catch (Exception e) {
			throw new AppPersProviderException("Ошибка выполнения запроса!", e);
		}
	}
	
	@Override
	public int update(String sql) throws AppPersProviderException {
		try{
			initStatment();
			return stmt.executeUpdate(sql);
		} catch (SQLException e) {
			throw new AppPersProviderException("Ошибка выполнения запроса!", e);
		} catch (Exception e) {
			throw new AppPersProviderException("Ошибка выполнения запроса!", e);
		}
	}
	
	@Override
	public void exec(String sql) throws AppPersProviderException {
		try{
			initStatment();
			stmt.execute(sql);
		} catch (SQLException e) {
			throw new AppPersProviderException("Ошибка выполнения запроса!", e);
		} catch (Exception e) {
			throw new AppPersProviderException("Ошибка выполнения запроса!", e);
		}
	}
	

	@Override
	public void close() {
		try{
			if (rs!=null){
				rs.close();
				rs=null;
			}
		} catch (Exception e) {
			logger.error(e);
		}
		try{
			if (stmt!=null){
				try{
					stmt.cancel();
				} catch (Exception e) {
					logger.error(e);
				}
				stmt.close();
				stmt=null;
			}
		} catch (Exception e) {
			logger.error(e);
		}
		try{
			if (conn!=null){
				conn.close();
				if (!conn.isClosed()) {
					logger.error("Connection not closed after close!");
				}
			}
			conn=null;
		} catch (Exception e) {
			logger.error(e);
		}
	}

	@Override
	public boolean next() throws AppPersProviderException {
		try{
			if (rs!=null){
				return rs.next();
			} else {
				return false;
			}
		} catch (SQLException e) {
			throw new AppPersProviderException(e);
		}
	}
	
	@Override
	public void beginTransaction() throws AppPersProviderException {
		try{
			conn.setAutoCommit(false);
			stmt = (Statement) conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
				ResultSet.CONCUR_UPDATABLE);
		} catch (Exception e) {
			throw new AppPersProviderException(e);
		}
	}
	
	@Override
	public void commit() throws AppPersProviderException {
		try{
			conn.commit();
		} catch (Exception e) {
			throw new AppPersProviderException(e);
		}
	}
	
	@Override
	public void rollback() throws AppPersProviderException {
		try{
			conn.rollback();
		} catch (Exception e) {
			throw new AppPersProviderException(e);
		}
	}
	
	private void bindParam(PreparedStatement prpStmt, Object param, int index) throws SQLException {
		if (param instanceof Long) {
			prpStmt.setLong(index, (Long)param);
		} else if (param instanceof Integer) {
			prpStmt.setInt(index, (Integer)param);
		} else if (param instanceof Byte) {
			prpStmt.setByte(index, (Byte)param);
		} else if (param instanceof String) {
			prpStmt.setString(index, (String)param);
		} else if (param instanceof java.sql.Date) {
			prpStmt.setDate(index, (java.sql.Date)param);
		} else if (param instanceof Blob) {
			prpStmt.setBlob(index, (Blob)param);
		} else if (param instanceof java.util.Date) {
			prpStmt.setTimestamp(index, 
				new Timestamp(((java.util.Date) param).getTime()));
		} else if (param instanceof BigDecimal) {
			prpStmt.setBigDecimal(index, (BigDecimal)param);
		} else if (param instanceof Boolean) {
			prpStmt.setBoolean(index, (Boolean)param);
		} else if (param instanceof Double) {
			prpStmt.setDouble(index, (Double)param);
		} else if (param instanceof Float) {
			prpStmt.setFloat(index, (Float)param);
		} else if (param instanceof Short) {
			prpStmt.setShort(index, (Short)param);
		} else if (param instanceof Timestamp) {
			prpStmt.setTimestamp(index, (Timestamp)param);
		} else if (param instanceof Time) {
			prpStmt.setTime(index, (Time)param);
		} else if (param instanceof Enum) {
			prpStmt.setString(index, param.toString());
		} else {
			prpStmt.setObject(index, param);
			logger.error("Неизвестный тип параметра: " + param, new AppPersProviderException());
		}
	}
	
	public long insert(String sql, Object[] parameters) throws AppPersProviderException {
		try{
			PreparedStatement prpStmt = (PreparedStatement)
				conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			for (int i = 0; i < parameters.length; i++) {
				bindParam(prpStmt, parameters[i], i + 1);
			}
			prpStmt.executeUpdate();
			long last = -1;
			refreshResultSet();
			rs = prpStmt.getGeneratedKeys();
			if (rs.next()){
				last = rs.getLong(1);
			}
			return last;
		} catch (SQLException e) {
			throw new AppPersProviderException("Ошибка выполнения запроса!", e);
		} catch (Exception e) {
			throw new AppPersProviderException("Ошибка выполнения запроса!", e);
		}
	}
	
	public int update(String sql, Object[] parameters) throws AppPersProviderException {
		try{
			PreparedStatement prpStmt = (PreparedStatement) conn.prepareStatement(sql);
			for (int i = 0; i < parameters.length; i++) {
				bindParam(prpStmt, parameters[i], i + 1);
			}
			return prpStmt.executeUpdate();
		} catch (SQLException e) {
			throw new AppPersProviderException("Ошибка выполнения запроса!", e);
		} catch (Exception e) {
			throw new AppPersProviderException("Ошибка выполнения запроса!", e);
		}
	}
}